//
//  FMDBHelper.swift
//  ANOA
//
//  Created by 邱仙凯 on 2023/9/21.
//  Copyright © 2023 zwb. All rights reserved.
//
//

import UIKit
 import FMDB
final class FMDBHelper {
    
    private init() {}
    static let shared = FMDBHelper()
    var database:FMDatabase!
    var databaseURL:URL!
    
 
    func openDB(dbname:String) {
        
        let fileManager = FileManager.default
        let docsDirURL = try! fileManager.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
        databaseURL = docsDirURL.appendingPathComponent(dbname)
        
        database = FMDatabase(path: databaseURL.path)
       if !database.isOpen {
           database.open()
       }
   
    }
    
    @discardableResult
    func creatTable(tableName:String,params:[String]) -> Bool {
        var sql = "CREATE TABLE IF NOT EXISTS \(tableName) (id INTEGER PRIMARY KEY"
        
        for i in 0..<params.count {
            let item = params[i]
            if i == params.count - 1 {
                sql += ", \(item) TEXT)"
            } else {
                sql += ", \(item) TEXT"
            }
        }
        print(sql)
        if !database.tableExists(tableName) {
            return database.executeStatements(sql)
        } else {
            return false
        }
        
    }
    @discardableResult
    func insertInto(table:String,paramValues:[(String,Any)]) -> Int64? {
        
        let params = paramValues.compactMap{$0.0}.joined(separator: ",")
        let values = paramValues.compactMap{$0.1}
        
        let zhanweis = paramValues.compactMap{ _ in "?" }.joined(separator: ",")
        
        
        let sql = "INSERT INTO \(table) (\(params)) VALUES (\(zhanweis))"
        
        
        if database.executeUpdate(sql, withArgumentsIn: values) {
            // 获取最后插入行的 ID
            return  database.lastInsertRowId
        } else {
            return nil
        }
     
        
    }
    
    
    @discardableResult
    func delete(from table:String) -> Bool {
        // 构建 DELETE SQL 语句
        
        let deleteSQL = "DELETE FROM \(table)"
        //        let deleteSQL = "DELETE FROM CHAT_HISTORY"
        
        // 使用 executeUpdate 方法执行 SQL 语句，并通过 withArgumentsInArray 传递参数（防止 SQL 注入）
        if !database.executeUpdate(deleteSQL, withArgumentsIn: [""]) {
            // 如果执行失败，打印错误信息
            print("Error deleting data: \(database.lastErrorMessage())")
            return false
        } else {
            // 如果执行成功，打印确认信息
            print("Data deleted successfully.")
            return true
        }
        
    }
    @discardableResult
    func delete(from table:String,where param:String,equalto str:String) -> Bool {
        // 构建 DELETE SQL 语句
        
        let deleteSQL = "DELETE FROM \(table) WHERE \(param) = ?"
        //        let deleteSQL = "DELETE FROM CHAT_HISTORY"
        
        // 使用 executeUpdate 方法执行 SQL 语句，并通过 withArgumentsInArray 传递参数（防止 SQL 注入）
        if !database.executeUpdate(deleteSQL, withArgumentsIn: [str]) {
            // 如果执行失败，打印错误信息
            print("Error deleting data: \(database.lastErrorMessage())")
            return false
        } else {
            // 如果执行成功，打印确认信息
            print("Data deleted successfully.")
            return true
        }
        
    }
    @discardableResult
    func update(table:String,set param1:[String],with value1:[String], where param2:String,equalto value2:String) -> Bool {
        
        var pp1 = ""
        for i in 0..<param1.count {
            pp1 += "\(param1[i]) = ?"
            if i != param1.count - 1 {
                pp1 += ","
            }
        }
        
        let updateSQL = "UPDATE \(table) SET \(pp1) WHERE \(param2) = ?"
        KSLog("update_sql")

        KSLog(updateSQL)
        let vvs = value1 + [value2]
        KSLog(vvs)
//        let newEmail: String = "newemail@example.com"
//          
//        // 构造 UPDATE 语句
//        let updateSQL = "UPDATE users SET name = ?, email = ? WHERE id = ?"
//          
//        // 执行语句并绑定参数
//        do {
//            try database.executeUpdate(updateSQL, withArgumentsIn: [newName, newEmail, userID])
//            print("更新成功")
//        } catch {
//            print("更新失败: \(error.localizedDescription)")
//        }
//          
        
        
        
        return  database.executeUpdate(updateSQL, withArgumentsIn: vvs )
    }
    
    
    func closeDB() {
        database.close()
    }
    func saveMediaToFileSystem(medias:[VideoImg]) -> [String] {
        var paths:[String] = []
        let fileManager = FileManager.default
        let urls = fileManager.urls(for: .documentDirectory, in: .userDomainMask)
        if let documentDirectory = urls.first  {
        
        for item in medias {
            
            mirror(item)
            let fileURL = documentDirectory.appendingPathComponent("\(item.name.unwrap)")
            mirror(fileURL)
            do {
                try item.data!.write(to: fileURL)
                paths.append(fileURL.path)
            } catch {
                print("Error saving media to file system")
                paths.append("")
            }
        }
        }
       return paths
    }

}


 
